﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using SPC.Properties;

namespace SPC.Classes
{
    class PersonEvents
    {
        public static string PERSONS_TABLE = "Persons";

        public PersonEvents() { }

        public List<Person> GetAllPersons()
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable person = new DataTable();
                String query = "select *";
                query += " from " + PERSONS_TABLE + ";";
                person = db.GetDataTable(query);
                // The results can be directly applied to a DataGridView control
                //personDataGrid.DataSource = person;
                

                List<Person> listPersons = new List<Person>();
                // Or looped through for some other reason
                foreach (DataRow r in person.Rows)
                {
                    Person objPerson = new Person();
                    objPerson.setID(r["id"].ToString());
                    objPerson.setName(r[Properties.Settings.Default.PERSON_ATTRIB_NAME].ToString());
                    objPerson.setAddress(r[Properties.Settings.Default.PERSON_ATTRIB_ADDRESS].ToString());
                    listPersons.Add(objPerson);
                }


                return listPersons;
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public Person GetAllPerson(int intPersonID)
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable person = new DataTable();
                String query = "select *";
                query += " from " + PERSONS_TABLE;
                if (intPersonID > 0) query += " where id = " + intPersonID + ";";
                person = db.GetDataTable(query);
                // The results can be directly applied to a DataGridView control

                List<Person> listPersons = new List<Person>();
                // Or looped through for some other reason
                foreach (DataRow r in person.Rows)
                {
                    Person objPerson = new Person();
                    objPerson.setID(r["id"].ToString());
                    objPerson.setName(r[Properties.Settings.Default.PERSON_ATTRIB_NAME].ToString());
                    objPerson.setAddress(r[Properties.Settings.Default.PERSON_ATTRIB_ADDRESS].ToString());
                    listPersons.Add(objPerson);
                    break;
                }
                return listPersons[0];
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public DataTable GetAllPersons2GridView()
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable person = new DataTable();
                String query = "select id, " + Properties.Settings.Default.PERSON_ATTRIB_NAME + ", " + Properties.Settings.Default.PERSON_ATTRIB_ADDRESS;
                query += " from " + PERSONS_TABLE + ";";
                //MessageBox.Show(query);
                person = db.GetDataTable(query);
                return person;
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public void Update(Person objPerson)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<String, String> data = new Dictionary<String, String>();
            //DataTable rows;
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_NAME, objPerson.getName());
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_ADDRESS, objPerson.getAddress());
            try
            {
                db.Update(PERSONS_TABLE, data, "id = " + objPerson.getID());
            }
            catch (Exception crap)
            {
                MessageBox.Show(crap.Message);
            }
            //db.

        }

        public void Insert(Person objPerson)
        {

            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<String, String> data = new Dictionary<String, String>();
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_NAME, objPerson.getName());
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_ADDRESS, objPerson.getAddress());
            try
            {
                db.Insert(PERSONS_TABLE, data);
            }
            catch (Exception crap)
            {
                MessageBox.Show(crap.Message);
            }
        }

        public void Delete(String strPersonID)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            db.Delete(PERSONS_TABLE, "id = "+strPersonID);
            db.Delete(PenaltyEvents.PENALTY_TABLE, "person_id = " + strPersonID);
        }

    }
}
